package com.lhyl.front.dao;

import java.sql.SQLException; 
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.RowSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException; 
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import com.lhyl.front.common.RegUtils;
import com.lhyl.front.common.StringUtils;
import com.lhyl.front.constant.FrontConstant;
import com.lhyl.front.dao.dialect.DataBaseDialectFactory;
import com.lhyl.front.dao.dialect.DataBasePagingDialectService;
import com.lhyl.front.enums.DataBaseDialectEnum;
import com.lhyl.front.exception.SystemDBException;
import com.lhyl.front.model.PageModel;
import com.lhyl.front.report.RepDataHandlerProvider;
import com.lhyl.front.report.RepDataHandlerService;



 
/****
 * 使用原生态的SQL语句查询 
 * @author hongwu.huang
 *
 */
public class CmsJdbcBaseDao extends NamedParameterJdbcDaoSupport {
 
 
	@Autowired
	private RepDataHandlerProvider repDataHandlerProvider;

	 
	
	/***
	 * 根据RepCode获取对应的SQL
	 * 
	 * @param repCode
	 * @throws SystemDBException
	 */
	private Map<String, Object> getRepCode(String repCode) throws SystemDBException { 
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT REP_CODE,REP_NAME,REP_SQL,REP_HANDLER FROM TR_REP_CONF WHERE REP_CODE=:REP_CODE ");
		Map<String, Object> queryMap = new HashMap<String, Object>();
		queryMap.put("REP_CODE", repCode);
		Map<String, Object> repMap = this.getNamedParameterJdbcTemplate().queryForMap(sb.toString(), queryMap);
		if (repMap != null && !repMap.isEmpty()) {
			return repMap;
		} else {
			throw new SystemDBException("通过[" + repCode + "]查找失败，请检查你的配置是否正确。");
		}
	}

	/****
	 * 根据RepCode 查询数据
	 * 
	 * @param repCode
	 * @param query
	 * @return
	 * @throws Exception
	 * @throws DataAccessException
	 */
	public Object getRepResult(String repCode, final Map<String, Object> queryParam) throws SystemDBException {
		Map<String, Object> repMap = this.getRepCode(repCode);
		String rsSql = StringUtils.obj2String(repMap.get("REP_SQL"));
		String handlerBean = StringUtils.obj2String(repMap.get("REP_HANDLER"));
		RepDataHandlerService handlerService = repDataHandlerProvider.getRepDataHandlerBean(handlerBean);
		RegUtils.findAndReplace("\\:\\s*[a-zA-Z\\.0-9\\_\\-\\?\\*\\/]+\\s*", rsSql, new RegUtils.FindCallback() {
			public String execute(String source) {
				String s = StringUtils.trim(StringUtils.replace(source, ":", ""));
				if (queryParam.get(s) == null) {
					queryParam.put(s, "");
				}
				return source;
			}
		});
		List<Map<String, Object>> result = this.getResultBySql(rsSql, queryParam);
		if (handlerService == null) {
			return result;
		}
		try {
			return handlerService.handlerRepData(result,queryParam);
		} catch (Exception e) {
			 throw new SystemDBException("查询数据异常:"+e.getMessage()) ;
		}
	}
	
	
	/***
	 *  根据传入的SQL语句  查询的参数 执行SQL查询 返回 查询结果 
	 * @param rsSql
	 * @param queryParam
	 * @return
	 */
	public List<Map<String,Object>>getResultBySql(String rsSql,Map<String, Object>queryParam)
	{ 
		return this.getNamedParameterJdbcTemplate().queryForList(rsSql, queryParam);
	}
	
	/****
	 *  根据SQL语句获 查询的行数
	 * @param rsSql
	 * @param queryParam
	 * @return
	 */
	public int getResultCountBySql(String rsSql,Map<String, Object>queryParam)
	{
		return this.getNamedParameterJdbcTemplate().queryForInt(rsSql, queryParam);
	}
	
	/***
	 * 
	 * @param rsSql
	 * @param queryParam
	 * @return
	 */
	public Map<String,Object> getSingleResultByOrgSql(String rsSql,Map<String, Object>queryParam)
	{
		return this.getNamedParameterJdbcTemplate().queryForMap(rsSql, queryParam);
	}
	
	/***
	 * 
	 * @param repCode
	 * @param queryParam
	 * @return
	 */
	public PageModel pagingResult(String repCode,final Map<String, Object>queryParam)throws SystemDBException
	{
		PageModel model = new PageModel();
		try {
			Map<String, Object> repMap = this.getRepCode(repCode);
			String rsSql = StringUtils.obj2String(repMap.get("REP_SQL"));
			String handlerBean = StringUtils.obj2String(repMap.get("REP_HANDLER"));
			String pageIndex=StringUtils.obj2String(queryParam.get(FrontConstant.PAGE_INDEX));
			String pageSize =StringUtils.obj2String(queryParam.get(FrontConstant.PAGE_SIZE));
			DataBaseDialectEnum dbType = this.getDialect();
			DataBasePagingDialectService db =DataBaseDialectFactory.getInstance().createDialectService(dbType); 
			RegUtils.findAndReplace("\\:\\s*[a-zA-Z\\.0-9\\_\\-\\?\\*\\/]+\\s*", rsSql, new RegUtils.FindCallback() {
				public String execute(String source) {
					String s = StringUtils.trim(StringUtils.replace(source, ":", ""));
					if (queryParam.get(s) == null) {
						queryParam.put(s, "");
					}
					return source;
				}
			});
			queryParam.put(FrontConstant.PAGE_START_INDEX, (StringUtils.str2Int(pageIndex)-1)*StringUtils.str2Int(pageSize));
			queryParam.put(FrontConstant.PAGE_END_INDEX, StringUtils.str2Int(pageSize));
			String sql = db.generatePagingSQL(rsSql); 
			RepDataHandlerService handlerService = repDataHandlerProvider.getRepDataHandlerBean(handlerBean);
			model.setTotalItem(this.getResultCountBySql(db.generatePagingCountSQL(rsSql), queryParam));
			if (handlerService == null) {
				model.setList(this.getResultBySql(sql,queryParam));
			}
			else
			{
				model.setList((List)handlerService.handlerRepData(this.getResultBySql(sql,queryParam),queryParam));
			}   
		} catch (Exception e) {
			throw new SystemDBException("执行分页查询数据异常:repCode:" + repCode + ";异常信息" + e.getMessage());
		}
		return model;
	}
	
	
	/***
	 * 
	 * 根据repCode获取单条记录
	 * @return
	 * @throws SQLException
	 */
	public Map<String,Object> querySingleResult(String repCode,final Map<String,Object>queryParam) throws  SystemDBException
	{
		Map<String, Object> repMap = this.getRepCode(repCode);
		String rsSql = StringUtils.obj2String(repMap.get("REP_SQL"));
		RegUtils.findAndReplace("\\:\\s*[a-zA-Z\\.0-9\\_\\-\\?\\*\\/]+\\s*", rsSql, new RegUtils.FindCallback() {
			public String execute(String source) {
				String s = StringUtils.trim(StringUtils.replace(source, ":", ""));
				if (queryParam.get(s) == null) {
					queryParam.put(s, "");
				}
				return source;
			}
		});
		return this.getNamedParameterJdbcTemplate().queryForMap(rsSql, queryParam);
	}
	
	
	/***
	 * 
	 * 获取dbType
	 * @return
	 * @throws SQLException
	 */
	private DataBaseDialectEnum getDialect() throws  SQLException
	{
		String dbtype =super.getConnection().getMetaData().getDatabaseProductName();
		return DataBaseDialectEnum.valueOf(dbtype);
	}
}
